from sqlalchemy import select
from datetime import datetime
from . import engine, session_factory
from .base import Base
from .author import Author
from .book import Book
from .student import Student
from .receiving_book import ReceivingBook


class BookLibrary:
    @staticmethod
    def drop_and_create_tables():
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)

    @staticmethod
    def insert_authors_and_books():
        with session_factory() as session:
            # Введём в БД Льва Толстого и три его книги
            author_1 = Author(first_name="Лев", last_name="Толстой")
            book_1 = Book(
                title="Война и мир", count=5,
                release_date=datetime.strptime('1867-12-01', '%Y-%m-%d'),
            )
            book_2 = Book(
                title="Анна Каренина", count=10,
                release_date=datetime.strptime('1877-11-13', '%Y-%m-%d')
            )
            book_3 = Book(
                title="Кавказский пленник", count=15,
                release_date=datetime.strptime('1872-05-05', '%Y-%m-%d')
            )
            author_1.books.append(book_1)
            author_1.books.append(book_2)
            author_1.books.append(book_3)

            # Введём в БД Михаила Булгакова и три его книги
            author_2 = Author(first_name="Михаил", last_name="Булгаков")
            book_4 = Book(
                title="Собачье сердце", count=20,
                release_date=datetime.strptime('1925-01-01', '%Y-%m-%d'),
            )
            book_5 = Book(
                title="Белая гвардия", count=22,
                release_date=datetime.strptime('1925-11-21', '%Y-%m-%d'),
            )
            book_6 = Book(
                title="Мастер и Маргарита", count=27,
                release_date=datetime.strptime('1928-05-15', '%Y-%m-%d'),
            )
            author_2.books.append(book_4)
            author_2.books.append(book_5)
            author_2.books.append(book_6)

            # Введём в БД Ивана Бунина и три его книги
            author_3 = Author(first_name="Иван", last_name="Бунин")
            book_7 = Book(
                title="Тёмные аллеи", count=14,
                release_date=datetime.strptime('1938-02-17', '%Y-%m-%d'),
            )
            book_8 = Book(
                title="Митина любовь", count=17,
                release_date=datetime.strptime('1925-12-10', '%Y-%m-%d'),
            )
            book_9 = Book(
                title="Дело корнета Елагина", count=19,
                release_date=datetime.strptime('1938-02-17', '%Y-%m-%d'),
            )
            author_3.books.append(book_7)
            author_3.books.append(book_8)
            author_3.books.append(book_9)

            session.add_all([author_1, author_2, author_3])
            session.commit()

    @staticmethod
    def insert_students():
        with session_factory() as session:
            # оказывается тип int - есть ограничения по максимальному числу, максимум - 2147483647
            student_1 = Student(first_name="Иван", last_name="Иванов", phone="+79021927550", email="ivan@gmail.com", average_score=1)
            student_2 = Student(first_name="Пётр", last_name="Петров", phone="+79021927551", email="pyotr@gmail.com", average_score=2)
            student_3 = Student(first_name="Павел", last_name="Судоплатов", phone="+79021927552", email="pavel@gmail.com", average_score=3)
            student_4 = Student(first_name="Мария", last_name="Шукшина", phone="+79021927553", email="maria@gmail.com", average_score=4)
            student_5 = Student(first_name="Ольга", last_name="Васильева", phone="+79021927554", email="olga_vas@gmail.com", average_score=5)
            student_6 = Student(first_name="Ольга", last_name="Филипова", phone="+79021927555", email="olga@gmail.com", average_score=5)
            student_7 = Student(first_name="Людмила", last_name="Путина", phone="+79021927556", email="ludmila@gmail.com", average_score=6)
            student_8 = Student(first_name="Кирилл", last_name="Петров-Водкин", phone="+79021927557", email="kirill@gmail.com", average_score=5)
            student_9 = Student(first_name="Николай", last_name="Суслов", phone="+79021927558", email="nikoay@gmail.com", average_score=3)
            student_10 = Student(first_name="Глеб", last_name="Чижиков", phone="+79021927559", email="gleb@gmail.com", average_score=7)
            session.add_all([student_1, student_2, student_3, student_4, student_5, student_6, student_7, student_8, student_9, student_10])
            session.commit()

    @staticmethod
    def insert_receiving_books():
        with session_factory() as session:
            rb_1 = ReceivingBook(student_id=1, book_id=1, date_of_issue='2024-04-01', date_of_return='2024-04-15')
            rb_2 = ReceivingBook(student_id=2, book_id=3, date_of_issue='2024-05-10', date_of_return='2024-05-21')
            rb_3 = ReceivingBook(student_id=3, book_id=2, date_of_issue='2024-06-01')
            rb_4 = ReceivingBook(student_id=4, book_id=1, date_of_issue='2024-04-01', date_of_return='2024-04-15')
            rb_5 = ReceivingBook(student_id=5, book_id=6, date_of_issue='2024-06-01')
            rb_6 = ReceivingBook(student_id=6, book_id=2, date_of_issue='2024-05-01', date_of_return='2024-05-15')
            rb_7 = ReceivingBook(student_id=7, book_id=6, date_of_issue='2024-06-01')
            rb_8 = ReceivingBook(student_id=7, book_id=3, date_of_issue='2024-01-01', date_of_return='2024-04-15')
            rb_9 = ReceivingBook(student_id=7, book_id=2, date_of_issue='2024-02-01', date_of_return='2024-04-21')
            rb_10 = ReceivingBook(student_id=10, book_id=1, date_of_issue='2024-01-01', date_of_return='2024-01-15')
            rb_11 = ReceivingBook(student_id=1, book_id=3, date_of_issue='2024-01-01', date_of_return='2024-04-15')
            rb_12 = ReceivingBook(student_id=1, book_id=2, date_of_issue='2024-02-01', date_of_return='2024-04-21')
            rb_13 = ReceivingBook(student_id=1, book_id=5, date_of_issue='2024-01-01', date_of_return='2024-01-15')
            session.add_all([rb_1, rb_2, rb_3, rb_4, rb_5, rb_6, rb_7, rb_8, rb_9, rb_10, rb_11, rb_12, rb_13])
            session.commit()

    @staticmethod
    def select_from_database():
        with session_factory() as session:
            result = session.execute(select(Author)).unique()
            authors = result.all()
            result = session.execute(select(Book))
            books = result.all()
            result = session.execute(select(Student)).unique()
            students = result.all()
            # result = session.execute(select(ReceivingBooks))
            # receiving_books = result.all()
            print('Авторы книг:')
            for i in authors:
                print(f"ID:{i[0].id} Имя: {i[0].first_name}, Фамилия: {i[0].last_name}")
            print('\nКниги:')
            for i in books:
                print(f"ID:{i[0].id} Название: {i[0].title}. Количество книг: {i[0].count}")
            print('\nСтуденты:')
            for i in students:
                print(f"ID:{i[0].id} Имя: {i[0].first_name}, Фамилия: {i[0].last_name}")
